import pandas as pd
from pandas import DataFrame, Series
import numpy as np
import os

emp_info = DataFrame(data=
                     {"姓名": ["张三", "李四", "王五", "赵六", "赵七", "钱八"],
                      "物理": np.random.randint(0, 100, size=6),
                      "化学": np.random.randint(0, 100, size=6),
                      "数学": np.random.randint(0, 100, size=6)},
                     index=np.arange(1, 7))
file_path = './emp.xlsx'
if not os.path.exists(file_path):
    file = open(file_path, "w")
emp_info.to_excel(file_path)
print('emp_info\n', emp_info)
vfile = pd.read_excel(file_path, index_col=0)
print('vfile\n', vfile)
df = pd.DataFrame(vfile)
sum_score = df.sum(axis=1, numeric_only=True)
avg_score = df.mean(axis=1, numeric_only=True)
df["总分"] = sum_score
df["平均分"] = avg_score
df.to_excel(file_path)
